package com.redpack.redpost.util.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import com.redpack.redpost.connection.RedpostConexion;
import com.redpack.redpost.util.to.*;

public class ReporteNewsletterDao {

	
	public static List getReporteArribo(String fecha_inicio,String fecha_final,String nip,String SelectCheckboxPromos) throws Exception {
		
		Connection conn = RedpostConexion.openConnection( ) ;
		conn.setAutoCommit( false ) ;
		PreparedStatement ps = null ;
		ResultSet rs = null ;
		List _ReporteArribo = new ArrayList(); 
		String sql = "" ;
		
		String cond = "" ;
		
		try{			
			
			sql += "SELECT NL.cve_newsletter, NL.cve_usuarioredpost,UR.nip, NL.nombre, NL.apellidos, NL.email, ";
			
			sql += "CASE WHEN NL.articulos_deportivos = 1 THEN 'Activo' ELSE 'Desactivado' END as articulos_deportivos, ";
			sql += "CASE WHEN NL.instrumentos_musicales = 1 THEN 'Activo' ELSE 'Desactivado' END as instrumentos_musicales, ";
			sql += "CASE WHEN NL.peliculas = 1 THEN 'Activo' ELSE 'Desactivado' END as peliculas, ";
			sql += "CASE WHEN NL.autopartes = 1 THEN 'Activo' ELSE 'Desactivado' END as autopartes, ";
			sql += "CASE WHEN NL.jardines_exteriores = 1 THEN 'Activo' ELSE 'Desactivado' END as jardines_exteriores, ";
			sql += "CASE WHEN NL.provisiones_gourmet = 1 THEN 'Activo' ELSE 'Desactivado' END as provisiones_gourmet, ";
			sql += "CASE WHEN NL.bebes_ninios = 1 THEN 'Activo' ELSE 'Desactivado' END as bebes_ninios, ";
			
			
			sql += "CASE WHEN NL.joyeria_relojes = 1 THEN 'Activo' ELSE 'Desactivado' END as joyeria_relojes, ";
			sql += "CASE WHEN NL.revistas = 1 THEN 'Activo' ELSE 'Desactivado' END as revistas, ";
			sql += "CASE WHEN NL.camaras = 1 THEN 'Activo' ELSE 'Desactivado' END as camaras, ";
			
			sql += "CASE WHEN NL.jugetes = 1 THEN 'Activo' ELSE 'Desactivado' END as jugetes, ";
			sql += "CASE WHEN NL.ropa = 1 THEN 'Activo' ELSE 'Desactivado' END as ropa, ";
			sql += "CASE WHEN NL.casa_interiores = 1 THEN 'Activo' ELSE 'Desactivado' END as casa_interiores, ";
			sql += "CASE WHEN NL.libros = 1 THEN 'Activo' ELSE 'Desactivado' END as libros, ";
			
			
			sql += "CASE WHEN NL.salud_belleza = 1 THEN 'Activo' ELSE 'Desactivado' END as salud_belleza, ";
			sql += "CASE WHEN NL.computadoras = 1 THEN 'Activo' ELSE 'Desactivado' END as computadoras, ";
			sql += "CASE WHEN NL.muebles = 1 THEN 'Activo' ELSE 'Desactivado' END as muebles, ";
			sql += "CASE WHEN NL.software = 1 THEN 'Activo' ELSE 'Desactivado' END as software, ";
			
			sql += "CASE WHEN NL.electronicos = 1 THEN 'Activo' ELSE 'Desactivado' END as electronicos, ";
			sql += "CASE WHEN NL.ofertas = 1 THEN 'Activo' ELSE 'Desactivado' END as ofertas, ";
			sql += "CASE WHEN NL.videojuegos = 1 THEN 'Activo' ELSE 'Desactivado' END as videojuegos, ";
			sql += "CASE WHEN NL.electrodomesticos = 1 THEN 'Activo' ELSE 'Desactivado' END as electrodomesticos, ";
			
			sql += "CASE WHEN NL.oficina = 1 THEN 'Activo' ELSE 'Desactivado' END as oficina, ";
			
			
			sql += "CASE WHEN NL.noticia_1 = 1 THEN 'Activo' ELSE 'Desactivado' END as noticia_1, ";
			sql += "CASE WHEN NL.noticia_2 = 1 THEN 'Activo' ELSE 'Desactivado' END as noticia_2, ";
			sql += "CASE WHEN NL.noticia_3 = 1 THEN 'Activo' ELSE 'Desactivado' END as noticia_3, ";
			sql += "CASE WHEN NL.noticia_4 = 1 THEN 'Activo' ELSE 'Desactivado' END as noticia_4, ";
			sql += "CASE WHEN NL.noticia_5 = 1 THEN 'Activo' ELSE 'Desactivado' END as noticia_5, ";
			sql += "CASE WHEN NL.noticia_6 = 1 THEN 'Activo' ELSE 'Desactivado' END as noticia_6, ";
			sql += "CASE WHEN NL.noticia_7 = 1 THEN 'Activo' ELSE 'Desactivado' END as noticia_7, ";
			sql += "CASE WHEN NL.noticia_8 = 1 THEN 'Activo' ELSE 'Desactivado' END as noticia_8, ";
			
			
			sql += "CASE WHEN NL.status = 1 THEN 'Activo' ELSE 'Desactivado' END as status, ";
			sql += "CASE WHEN NL.status = 1 THEN 'Desactivar' ELSE 'Activar' END as accion_status_texto, ";
			//accion = 1 Desactiva , accion = 2 Activar
			sql += "CASE WHEN NL.status = 1 THEN 1 ELSE 2 END as accion_status, ";
			sql += "NL.fechaderegistro ";
			sql += "FROM redpost.tbl_newsletter NL ";
			sql += " left outer join redpost.tbl_usuarioredpost UR on  UR.cve_usuarioredpost = NL.cve_usuarioredpost "; 
		

			if(fecha_inicio != "" && fecha_final != ""){
				if(!(cond == "")){
					cond += " and ";
				}
				cond += " NL.fechaderegistro >= '"+ fecha_inicio +"' and NL.fechaderegistro <= '"+ fecha_final +"' ";
			}
			if(fecha_inicio == "" && fecha_final != ""){
				if(!(cond == "")){
					cond += " and ";
				}
				cond += " NL.fechaderegistro = '"+ fecha_final +"' ";
			}
			if(fecha_inicio != "" && fecha_final == ""){
				if(!(cond == "")){
					cond += " and ";
				}
				cond += " NL.fechaderegistro = '"+ fecha_inicio +"' ";
			}

			if(nip != "" && nip != null){
				if(!(cond == "")){
					cond += " and ";
				}
				cond += " UR.nip = '"+ nip +"' ";
			}
			
			if(SelectCheckboxPromos != "" && SelectCheckboxPromos != null){
				if(!(cond == "")){
					cond += " and ";
				}
				
			
				String[] _CheckboxPromos = SelectCheckboxPromos.split(",");
				String _campo = "";
				for(int i = 0; i <= _CheckboxPromos.length-1;i++){
					
					_campo += ObtenerCampoPorId(_CheckboxPromos[i]);
					
					if(_campo != "" && (_CheckboxPromos.length-1 == 0)){
						_campo += "=1 ";
					}
					
					if(_campo != "" &&  (_CheckboxPromos.length-1 > 0) &&  i < _CheckboxPromos.length - 1){
						_campo += "=1 and ";
					}
					
					if(_campo != "" && (_CheckboxPromos.length-1 > 0) &&  i == _CheckboxPromos.length - 1){
						_campo += "=1  ";
					}
					
					
				}
				
				cond += _campo;
				
				
			}
			

			sql += "where ";
			sql += cond;
			sql += " order by NL.fechaderegistro desc  ";
			
			
			ps = conn.prepareStatement( sql ) ;
			rs = ps.executeQuery();


			while (rs.next()) {
				NewsletterTo row = new NewsletterTo();
				
				row.setCve_newsletter(rs.getInt("cve_newsletter"));
				row.setCve_usuarioredpost(rs.getInt("cve_usuarioredpost"));
				row.setNip(rs.getString("nip"));
				
				
				row.setNombre(rs.getString("nombre"));
				row.setApellidos(rs.getString("apellidos"));
				row.setEmail(rs.getString("email"));
			
				
				row.setArticulos_deportivos(rs.getString("articulos_deportivos"));
				row.setInstrumentos_musicales(rs.getString("instrumentos_musicales"));
				row.setPeliculas(rs.getString("peliculas"));
				row.setAutopartes(rs.getString("autopartes"));
				row.setJardines_exteriores(rs.getString("jardines_exteriores"));
				row.setProvisiones_gourmet(rs.getString("provisiones_gourmet"));
				
				row.setBebes_ninios(rs.getString("bebes_ninios"));
				row.setJoyeria_relojes(rs.getString("joyeria_relojes"));
				row.setRevistas(rs.getString("revistas"));
				row.setCamaras(rs.getString("camaras"));
				row.setJugetes(rs.getString("jugetes"));
				row.setRopa(rs.getString("ropa"));
				
				row.setCasa_interiores(rs.getString("casa_interiores"));
				row.setLibros(rs.getString("libros"));
				row.setSalud_belleza(rs.getString("salud_belleza"));
				row.setComputadoras(rs.getString("computadoras"));
				row.setMuebles(rs.getString("muebles"));
				row.setSoftware(rs.getString("software"));
				
				row.setElectronicos(rs.getString("electronicos"));
				row.setOfertas(rs.getString("ofertas"));
				row.setVideojuegos(rs.getString("videojuegos"));
				row.setElectrodomesticos(rs.getString("electrodomesticos"));
				row.setOficina(rs.getString("oficina"));
				
				row.setNoticia_1(rs.getString("noticia_1"));
				row.setNoticia_2(rs.getString("noticia_2"));
				row.setNoticia_3(rs.getString("noticia_3"));
				row.setNoticia_4(rs.getString("noticia_4"));
				row.setNoticia_5(rs.getString("noticia_5"));
				row.setNoticia_6(rs.getString("noticia_6"));
				row.setNoticia_7(rs.getString("noticia_7"));
				row.setNoticia_8(rs.getString("noticia_8"));
				
		
				
				row.setStatus(rs.getString("status"));
				row.setAccion_status_texto(rs.getString("accion_status_texto"));
				row.setAccion_status(rs.getInt("accion_status"));
				row.setFechaderegistro(rs.getString("fechaderegistro"));
				
				_ReporteArribo.add( row );
			}
			
			conn.commit( ) ;
			ps.close( ) ;
			conn.close( ) ;
			
			return _ReporteArribo ;
			
		}catch ( Exception e ) {
			e.printStackTrace( ) ;
			return _ReporteArribo = null ;
		} finally {
	        if (conn != null) {
	            conn.close();
	        }
			
		}
	}	
	
	
	public static boolean updateStatusNewsletter(int cve_newsletter,int accion_status)throws Exception {
		
		boolean res = false ;
		Connection conn = null;
		try
		{	
			conn = RedpostConexion.openConnection( ) ;
			conn.setAutoCommit( false ) ;
			PreparedStatement ps = null ;
			int status = 0;
			
			
			//status = 1 (Activo) -> Desactivar
			//status = 2 (Desactivado) -> Activar
			if(accion_status == 1){
				status = 0;
			}
			else{
				status = 1;
			}
			
			String sql = "" ;
				

					sql += "UPDATE redpost.tbl_newsletter ";
					sql += "   SET  ";
					sql += " status="+ status +" ";
					
					sql += " WHERE cve_newsletter ="+ cve_newsletter +"; ";
					
				
				ps = conn.prepareStatement( sql ) ;
				ps.execute( ) ;

				res = true ;
				
				conn.commit( ) ;
				ps.close( ) ;
				conn.close( ) ;
				
				return res ;
				
			}
			catch ( Exception e ) 
			{
				e.printStackTrace( ) ;			
				return res ;
			} 
			finally 
			{
	            if (conn != null) 
	            {
	                conn.close();
	            }
			}
			
	}
	

public static List getNewsletterPorId(int cve_newsletter) throws Exception {
		
		Connection conn = RedpostConexion.openConnection( ) ;
		conn.setAutoCommit( false ) ;
		PreparedStatement ps = null ;
		ResultSet rs = null ;
		List _ReporteArribo = new ArrayList(); 
		String sql = "" ;
		
		String cond = "" ;
		
		try{			
			
			sql += "SELECT NL.cve_newsletter, NL.cve_usuarioredpost,UR.nip, NL.nombre, NL.apellidos, NL.email, ";
			
			sql += "CASE WHEN NL.articulos_deportivos = 1 THEN 'Activo' ELSE 'Desactivado' END as articulos_deportivos, ";
			sql += "CASE WHEN NL.instrumentos_musicales = 1 THEN 'Activo' ELSE 'Desactivado' END as instrumentos_musicales, ";
			sql += "CASE WHEN NL.peliculas = 1 THEN 'Activo' ELSE 'Desactivado' END as peliculas, ";
			sql += "CASE WHEN NL.autopartes = 1 THEN 'Activo' ELSE 'Desactivado' END as autopartes, ";
			sql += "CASE WHEN NL.jardines_exteriores = 1 THEN 'Activo' ELSE 'Desactivado' END as jardines_exteriores, ";
			sql += "CASE WHEN NL.provisiones_gourmet = 1 THEN 'Activo' ELSE 'Desactivado' END as provisiones_gourmet, ";
			sql += "CASE WHEN NL.bebes_ninios = 1 THEN 'Activo' ELSE 'Desactivado' END as bebes_ninios, ";
			
			
			sql += "CASE WHEN NL.joyeria_relojes = 1 THEN 'Activo' ELSE 'Desactivado' END as joyeria_relojes, ";
			sql += "CASE WHEN NL.revistas = 1 THEN 'Activo' ELSE 'Desactivado' END as revistas, ";
			sql += "CASE WHEN NL.camaras = 1 THEN 'Activo' ELSE 'Desactivado' END as camaras, ";
			
			sql += "CASE WHEN NL.jugetes = 1 THEN 'Activo' ELSE 'Desactivado' END as jugetes, ";
			sql += "CASE WHEN NL.ropa = 1 THEN 'Activo' ELSE 'Desactivado' END as ropa, ";
			sql += "CASE WHEN NL.casa_interiores = 1 THEN 'Activo' ELSE 'Desactivado' END as casa_interiores, ";
			sql += "CASE WHEN NL.libros = 1 THEN 'Activo' ELSE 'Desactivado' END as libros, ";
			
			
			sql += "CASE WHEN NL.salud_belleza = 1 THEN 'Activo' ELSE 'Desactivado' END as salud_belleza, ";
			sql += "CASE WHEN NL.computadoras = 1 THEN 'Activo' ELSE 'Desactivado' END as computadoras, ";
			sql += "CASE WHEN NL.muebles = 1 THEN 'Activo' ELSE 'Desactivado' END as muebles, ";
			sql += "CASE WHEN NL.software = 1 THEN 'Activo' ELSE 'Desactivado' END as software, ";
			
			sql += "CASE WHEN NL.electronicos = 1 THEN 'Activo' ELSE 'Desactivado' END as electronicos, ";
			sql += "CASE WHEN NL.ofertas = 1 THEN 'Activo' ELSE 'Desactivado' END as ofertas, ";
			sql += "CASE WHEN NL.videojuegos = 1 THEN 'Activo' ELSE 'Desactivado' END as videojuegos, ";
			sql += "CASE WHEN NL.electrodomesticos = 1 THEN 'Activo' ELSE 'Desactivado' END as electrodomesticos, ";
			
			sql += "CASE WHEN NL.oficina = 1 THEN 'Activo' ELSE 'Desactivado' END as oficina, ";
			
			
			sql += "CASE WHEN NL.noticia_1 = 1 THEN 'Activo' ELSE 'Desactivado' END as noticia_1, ";
			sql += "CASE WHEN NL.noticia_2 = 1 THEN 'Activo' ELSE 'Desactivado' END as noticia_2, ";
			sql += "CASE WHEN NL.noticia_3 = 1 THEN 'Activo' ELSE 'Desactivado' END as noticia_3, ";
			sql += "CASE WHEN NL.noticia_4 = 1 THEN 'Activo' ELSE 'Desactivado' END as noticia_4, ";
			sql += "CASE WHEN NL.noticia_5 = 1 THEN 'Activo' ELSE 'Desactivado' END as noticia_5, ";
			sql += "CASE WHEN NL.noticia_6 = 1 THEN 'Activo' ELSE 'Desactivado' END as noticia_6, ";
			sql += "CASE WHEN NL.noticia_7 = 1 THEN 'Activo' ELSE 'Desactivado' END as noticia_7, ";
			sql += "CASE WHEN NL.noticia_8 = 1 THEN 'Activo' ELSE 'Desactivado' END as noticia_8, ";
			
			
			sql += "CASE WHEN NL.status = 1 THEN 'Activo' ELSE 'Desactivado' END as status, ";
			sql += "CASE WHEN NL.status = 1 THEN 'Desactivar' ELSE 'Activar' END as accion_status_texto, ";
			//accion = 1 Desactiva , accion = 2 Activar
			sql += "CASE WHEN NL.status = 1 THEN 1 ELSE 2 END as accion_status, ";
			sql += "NL.fechaderegistro ";
			sql += "FROM redpost.tbl_newsletter NL ";
			sql += "inner join redpost.tbl_usuarioredpost UR on  UR.cve_usuarioredpost = NL.cve_usuarioredpost "; 
		

			sql += "where ";
			sql += " NL.cve_newsletter =" + cve_newsletter;
			
			
			ps = conn.prepareStatement( sql ) ;
			rs = ps.executeQuery();


			while (rs.next()) {
				NewsletterTo row = new NewsletterTo();
				
				row.setCve_newsletter(rs.getInt("cve_newsletter"));
				row.setCve_usuarioredpost(rs.getInt("cve_usuarioredpost"));
				row.setNip(rs.getString("nip"));
				
				
				row.setNombre(rs.getString("nombre"));
				row.setApellidos(rs.getString("apellidos"));
				row.setEmail(rs.getString("email"));
			
				
				row.setArticulos_deportivos(rs.getString("articulos_deportivos"));
				row.setInstrumentos_musicales(rs.getString("instrumentos_musicales"));
				row.setPeliculas(rs.getString("peliculas"));
				row.setAutopartes(rs.getString("autopartes"));
				row.setJardines_exteriores(rs.getString("jardines_exteriores"));
				row.setProvisiones_gourmet(rs.getString("provisiones_gourmet"));
				
				row.setBebes_ninios(rs.getString("bebes_ninios"));
				row.setJoyeria_relojes(rs.getString("joyeria_relojes"));
				row.setRevistas(rs.getString("revistas"));
				row.setCamaras(rs.getString("camaras"));
				row.setJugetes(rs.getString("jugetes"));
				row.setRopa(rs.getString("ropa"));
				
				row.setCasa_interiores(rs.getString("casa_interiores"));
				row.setLibros(rs.getString("libros"));
				row.setSalud_belleza(rs.getString("salud_belleza"));
				row.setComputadoras(rs.getString("computadoras"));
				row.setMuebles(rs.getString("muebles"));
				row.setSoftware(rs.getString("software"));
				
				row.setElectronicos(rs.getString("electronicos"));
				row.setOfertas(rs.getString("ofertas"));
				row.setVideojuegos(rs.getString("videojuegos"));
				row.setElectrodomesticos(rs.getString("electrodomesticos"));
				row.setOficina(rs.getString("oficina"));
				
				row.setNoticia_1(rs.getString("noticia_1"));
				row.setNoticia_2(rs.getString("noticia_2"));
				row.setNoticia_3(rs.getString("noticia_3"));
				row.setNoticia_4(rs.getString("noticia_4"));
				row.setNoticia_5(rs.getString("noticia_5"));
				row.setNoticia_6(rs.getString("noticia_6"));
				row.setNoticia_7(rs.getString("noticia_7"));
				row.setNoticia_8(rs.getString("noticia_8"));
				
		
				
				row.setStatus(rs.getString("status"));
				row.setAccion_status_texto(rs.getString("accion_status_texto"));
				row.setAccion_status(rs.getInt("accion_status"));
				row.setFechaderegistro(rs.getString("fechaderegistro"));
				
				_ReporteArribo.add( row );
			}
			
			conn.commit( ) ;
			ps.close( ) ;
			conn.close( ) ;
			
			return _ReporteArribo ;
			
		}catch ( Exception e ) {
			e.printStackTrace( ) ;
			return _ReporteArribo = null ;
		} finally {
	        if (conn != null) {
	            conn.close();
	        }
			
		}
	}	


	public static String[][] ObtenerCampo(){
		String[][] CampoNewsletter = new String[31][2];
		CampoNewsletter[0][0] = "1";
		CampoNewsletter[0][1] = "NL.articulos_deportivos";
		CampoNewsletter[1][0] = "2";
		CampoNewsletter[1][1] = "NL.instrumentos_musicales";
		CampoNewsletter[2][0] = "3";
		CampoNewsletter[2][1] = "NL.peliculas";
		CampoNewsletter[3][0] = "4";
		CampoNewsletter[3][1] = "NL.autopartes";
		CampoNewsletter[4][0] = "5";
		CampoNewsletter[4][1] = "NL.jardines_exteriores";
		CampoNewsletter[5][0] = "6";
		CampoNewsletter[5][1] = "NL.provisiones_gourmet";
		CampoNewsletter[6][0] = "7";
		CampoNewsletter[6][1] = "NL.bebes_ninios";
		CampoNewsletter[7][0] = "8";
		CampoNewsletter[7][1] = "NL.joyeria_relojes";
		CampoNewsletter[8][0] = "9";
		CampoNewsletter[8][1] = "NL.revistas";
		CampoNewsletter[9][0] = "10";
		CampoNewsletter[9][1] = "NL.camaras";
		CampoNewsletter[10][0] = "11";
		CampoNewsletter[10][1] = "NL.jugetes";
		CampoNewsletter[11][0] = "12";
		CampoNewsletter[11][1] = "NL.ropa";
		CampoNewsletter[12][0] = "13";
		CampoNewsletter[12][1] = "NL.casa_interiores";
		CampoNewsletter[13][0] = "14";
		CampoNewsletter[13][1] = "NL.libros";
		CampoNewsletter[14][0] = "15";
		CampoNewsletter[14][1] = "NL.salud_belleza";
		CampoNewsletter[15][0] = "16";
		CampoNewsletter[15][1] = "NL.computadoras";
		CampoNewsletter[16][0] = "17";
		CampoNewsletter[16][1] = "NL.muebles";
		CampoNewsletter[17][0] = "18";
		CampoNewsletter[17][1] = "NL.software";
		CampoNewsletter[18][0] = "19";
		CampoNewsletter[18][1] = "NL.electronicos";
		CampoNewsletter[19][0] = "20";
		CampoNewsletter[19][1] = "NL.ofertas";
		CampoNewsletter[20][0] = "21";
		CampoNewsletter[20][1] = "NL.videojuegos";
		CampoNewsletter[21][0] = "22";
		CampoNewsletter[21][1] = "NL.electrodomesticos";
		CampoNewsletter[22][0] = "23";
		CampoNewsletter[22][1] = "NL.oficina";
		CampoNewsletter[23][0] = "24";
		CampoNewsletter[23][1] = "NL.noticia_1";
		CampoNewsletter[24][0] = "25";
		CampoNewsletter[24][1] = "NL.noticia_2";
		CampoNewsletter[25][0] = "26";
		CampoNewsletter[25][1] = "NL.noticia_3";
		CampoNewsletter[26][0] = "27";
		CampoNewsletter[26][1] = "NL.noticia_4";
		CampoNewsletter[27][0] = "28";
		CampoNewsletter[27][1] = "NL.noticia_5";
		CampoNewsletter[28][0] = "29";
		CampoNewsletter[28][1] = "NL.noticia_6";
		CampoNewsletter[29][0] = "30";
		CampoNewsletter[29][1] = "NL.noticia_7";
		CampoNewsletter[30][0] = "31";
		CampoNewsletter[30][1] = "NL.noticia_8";

		return CampoNewsletter;
	}
	
	public static String ObtenerCampoPorId( String Id ){
		String[][] CampoNewsletter = ObtenerCampo();
		for(int i = 0 ; i <= CampoNewsletter.length-1;i++){
			if( CampoNewsletter[i][0].equals( Id ) ){
				return CampoNewsletter[i][1]; 
			}
		}
		return "";
	}
	
}
